package metadata

import (
	"github.com/pingcap/errors"
	"github.com/romberli/go-util/constant"
	"github.com/romberli/go-util/middleware"
	"github.com/romberli/log"

	"github.com/romberli/das/global"
	"github.com/romberli/das/internal/dependency/metadata"
)

const (
	defaultClusterType = 1
)

var _ metadata.DBRepo = (*DBRepo)(nil)

type DBRepo struct {
	Database middleware.Pool
}

// NewDBRepo returns metadata.DBRepo with given middleware.Pool
func NewDBRepo(db middleware.Pool) metadata.DBRepo {
	return newDBRepo(db)
}

// NewDBRepoWithGlobal returns metadata.DBRepo with global mysql pool
func NewDBRepoWithGlobal() metadata.DBRepo {
	return newDBRepo(global.DASMySQLPool)
}

// newDBRepo returns *DBRepo with given middleware.Pool
func newDBRepo(db middleware.Pool) *DBRepo {
	return &DBRepo{db}
}

// Execute executes given command and placeholders on the middleware
func (dr *DBRepo) Execute(command string, args ...interface{}) (middleware.Result, error) {
	conn, err := dr.Database.Get()
	if err != nil {
		return nil, err
	}
	defer func() {
		err = conn.Close()
		if err != nil {
			log.Errorf("metadata DBRepo.Execute(): close database connection failed. err: \n%+v", err)
		}
	}()

	return conn.Execute(command, args...)
}

// Transaction returns a middleware.Transaction that could execute multiple commands as a transaction
func (dr *DBRepo) Transaction() (middleware.Transaction, error) {
	return dr.Database.Transaction()
}

// GetAll gets all databases from the middleware
func (dr *DBRepo) GetAll() ([]metadata.DB, error) {
	sql := `
		SELECT di.id, di.db_name, di.cluster_id, di.cluster_type, di.env_id, di.del_flag, di.create_time, di.last_update_time
		FROM t_meta_db_info di
		    INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id
		WHERE di.del_flag = 0
		  AND mci.del_flag = 0
		ORDER BY di.id ;
	`
	log.Debugf("metadata DBRepo.GetAll() sql: \n%s", sql)

	result, err := dr.Execute(sql)
	if err != nil {
		return nil, err
	}

	// init []metadata.DB
	dbList := make([]metadata.DB, result.RowNumber())
	for i := range dbList {
		dbList[i] = NewEmptyDBInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(dbList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return dbList, nil
}

// GetByEnv gets databases of given env id from the middleware
func (dr *DBRepo) GetByEnv(envID int) ([]metadata.DB, error) {
	sql := `
		SELECT di.id, di.db_name, di.cluster_id, di.cluster_type, di.env_id, di.del_flag, di.create_time, di.last_update_time
		FROM t_meta_db_info di
		    INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id
		WHERE di.del_flag = 0
		  AND mci.del_flag = 0
		  AND di.env_id = ?
		ORDER BY di.id ;
	`
	log.Debugf("metadata DBRepo.GetByEnv sql: \n%s", sql)

	result, err := dr.Execute(sql, envID)
	if err != nil {
		return nil, err
	}

	// init []metadata.DB
	dbList := make([]metadata.DB, result.RowNumber())
	for i := range dbList {
		dbList[i] = NewEmptyDBInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(dbList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return dbList, nil
}

// GetByID gets a database by the identity from the middleware
func (dr *DBRepo) GetByID(id int) (metadata.DB, error) {
	sql := `
		SELECT di.id, di.db_name, di.cluster_id, di.cluster_type, di.env_id, di.del_flag, di.create_time, di.last_update_time
		FROM t_meta_db_info di
		    INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id
		WHERE di.del_flag = 0
		  AND mci.del_flag = 0
		  AND di.id = ? ;
	`
	log.Debugf("metadata DBRepo.GetByID() sql: \n%s\nplaceholders: %s", sql, id)

	result, err := dr.Execute(sql, id)
	if err != nil {
		return nil, err
	}
	switch result.RowNumber() {
	case 0:
		return nil, errors.Errorf("metadata DBRepo.GetByID(): data does not exist, id: %d", id)
	case 1:
		dbInfo := NewEmptyDBInfoWithRepo(dr)
		// map to struct
		err = result.MapToStructByRowIndex(dbInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
		if err != nil {
			return nil, err
		}

		return dbInfo, nil
	default:
		return nil, errors.Errorf("metadata DBRepo.GetByID(): duplicate key exists, id: %d", id)
	}
}

// GetDBByNameAndClusterInfo gets a database by the db name and cluster info from the middleware
func (dr *DBRepo) GetDBByNameAndClusterInfo(name string, clusterID, clusterType int) (metadata.DB, error) {
	sql := `
		SELECT di.id, di.db_name, di.cluster_id, di.cluster_type, di.env_id, di.del_flag, di.create_time, di.last_update_time
		FROM t_meta_db_info di
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id
		WHERE di.del_flag = 0
		  AND mci.del_flag = 0
		  AND db_name = ?
		  AND cluster_id = ?
		  AND cluster_type = ? ;
	`
	log.Debugf("metadata DBRepo.GetDBByNameAndClusterInfo() sql: \n%s\nplaceholders: %s, %d, %d", sql, name, clusterID, clusterType)
	result, err := dr.Execute(sql, name, clusterID, clusterType)
	if err != nil {
		return nil, err
	}
	switch result.RowNumber() {
	case 0:
		return nil, errors.Errorf("metadata DBRepo.GetDBByNameAndClusterInfo(): data does not exist, db name: %s, cluster id: %d, cluster type: %d", name, clusterID, clusterType)
	case 1:
		dbInfo := NewEmptyDBInfoWithRepo(dr)
		// map to struct
		err = result.MapToStructByRowIndex(dbInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
		if err != nil {
			return nil, err
		}

		return dbInfo, nil
	default:
		return nil, errors.Errorf("metadata DBRepo.GetDBByNameAndClusterInfo(): duplicate entry exists, db name: %s, cluster id: %d, cluster type: %d", name, clusterID, clusterType)
	}
}

// GetDBByNameAndHostInfo gets a database by the db name and host info from the middleware
func (dr *DBRepo) GetDBByNameAndHostInfo(name, hostIP string, portNum int) (metadata.DB, error) {
	sql := `
		SELECT di.id, di.db_name, di.cluster_id, di.cluster_type, di.env_id, di.del_flag, di.create_time, di.last_update_time
		FROM t_meta_db_info di
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id
			INNER JOIN t_meta_mysql_server_info msi ON mci.id = msi.cluster_id
		WHERE di.del_flag = 0
		  AND mci.del_flag = 0
		  AND msi.del_flag = 0
		  AND di.cluster_type = 1
		  AND di.db_name = ?
		  AND msi.host_ip = ?
		  AND msi.port_num = ? ;
	`
	log.Debugf("metadata DBRepo.GetDBByNameAndHostInfo() sql: \n%s\nplaceholders: %s, %s, %d", sql, name, hostIP, portNum)
	result, err := dr.Execute(sql, name, hostIP, portNum)
	if err != nil {
		return nil, err
	}
	switch result.RowNumber() {
	case 0:
		return nil, errors.Errorf("metadata DBRepo.GetDBByNameAndHostInfo(): data does not exist, db name: %s, host ip: %s, port num: %d", name, hostIP, portNum)
	case 1:
		dbInfo := NewEmptyDBInfoWithRepo(dr)
		// map to struct
		err = result.MapToStructByRowIndex(dbInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
		if err != nil {
			return nil, err
		}

		return dbInfo, nil
	default:
		return nil, errors.Errorf("metadata DBRepo.GetDBByNameAndHostInfo(): duplicate entry exists, db name: %s, host ip: %s, port num: %d", name, hostIP, portNum)
	}
}

// GetDBsByHostInfo gets databases by the host info from the middleware
func (dr *DBRepo) GetDBsByHostInfo(hostIP string, portNum int) ([]metadata.DB, error) {
	sql := `
		SELECT di.id, di.db_name, di.cluster_id, di.cluster_type, di.env_id, di.del_flag, di.create_time, di.last_update_time
		FROM t_meta_db_info di
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id
			INNER JOIN t_meta_mysql_server_info msi ON mci.id = msi.cluster_id
		WHERE di.del_flag = 0
		  AND mci.del_flag = 0
		  AND msi.del_flag = 0
		  AND di.cluster_type = 1
		  AND msi.host_ip = ?
		  AND msi.port_num = ? ;
	`
	log.Debugf("metadata DBRepo.GetDBsByHostInfo() sql: \n%s\nplaceholders: %s, %d", sql, hostIP, portNum)
	result, err := dr.Execute(sql, hostIP, portNum)
	if err != nil {
		return nil, err
	}

	// init []metadata.DB
	dbList := make([]metadata.DB, result.RowNumber())
	for i := range dbList {
		dbList[i] = NewEmptyDBInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(dbList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return dbList, nil
}

// GetID gets the identity with given database name, cluster id and cluster type from the middleware
func (dr *DBRepo) GetID(dbName string, clusterID int, clusterType int) (int, error) {
	sql := `
		SELECT di.id
		FROM t_meta_db_info di
		    INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id
		WHERE di.del_flag = 0
		  AND mci.del_flag = 0
          AND di.db_name = ?
		  AND di.cluster_id = ?
		  AND di.cluster_type = ? ;
	`
	log.Debugf("metadata DBRepo.GetID() select sql: %s", sql)
	result, err := dr.Execute(sql, dbName, clusterID, clusterType)
	if err != nil {
		return constant.ZeroInt, err
	}

	return result.GetInt(constant.ZeroInt, constant.ZeroInt)
}

// GetMySQLClusterByID gets the mysql cluster of the given id from the middleware
func (dr *DBRepo) GetMySQLClusterByID(id int) (metadata.MySQLCluster, error) {
	sql := `
		SELECT mci.id, mci.cluster_name, mci.middleware_cluster_id, mci.monitor_system_id, mci.env_id, mci.del_flag, mci.create_time, mci.last_update_time
		FROM t_meta_mysql_cluster_info mci
			INNER JOIN t_meta_db_info di ON mci.id = di.cluster_id
		WHERE mci.del_flag = 0
		  AND di.del_flag = 0
		  AND di.id = ? ;
		
	`
	log.Debugf("metadata DBRepo.GetMySQLClusterByID() sql: \n%s\nplaceholders: %d", sql, id)

	result, err := dr.Execute(sql, id)
	if err != nil {
		return nil, err
	}

	switch result.RowNumber() {
	case 0:
		return nil, errors.Errorf("metadata DBRepo.GetMySQLClusterByID(): data does not exist, id: %d", id)
	case 1:
		mysqlClusterInfo := NewEmptyMySQLClusterInfoWithGlobal()
		// map to struct
		err = result.MapToStructByRowIndex(mysqlClusterInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
		if err != nil {
			return nil, err
		}
		return mysqlClusterInfo, nil
	default:
		return nil, errors.Errorf("metadata DBRepo.GetMySQLClusterByID(): duplicate key exists, id: %d", id)
	}
}

// GetMySQLServersByID gets the mysql servers of the given id from the middleware
func (dr *DBRepo) GetMySQLServersByID(id int) ([]metadata.MySQLServer, error) {
	sql := `
		SELECT msi.id,
			   msi.cluster_id,
			   msi.server_name,
			   msi.service_name,
			   msi.host_ip,
			   msi.port_num,
			   msi.deployment_type,
			   msi.deployment_region,
			   msi.deployment_org,
			   msi.network_zone,
			   msi.del_flag,
			   msi.create_time,
			   msi.last_update_time
		FROM t_meta_db_info di
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id AND di.cluster_type = ?
			INNER JOIN t_meta_mysql_server_info msi ON mci.id = msi.cluster_id
		WHERE di.del_flag = 0
		  AND mci.del_flag = 0
		  AND msi.del_flag = 0
		  AND di.id = ? ;
	`
	log.Debugf("metadata DBRepo.GetMySQLServersByID() sql: \n%s\nplaceholders: %d, %d", sql, defaultClusterType, id)
	result, err := dr.Execute(sql, defaultClusterType, id)
	if err != nil {
		return nil, err
	}

	if result.RowNumber() == constant.ZeroInt {
		return nil, errors.Errorf("metadata DBRepo.GetMySQLServersByID(): data does not exist, id: %d", id)
	}
	// init []metadata.MySQLServer
	mysqlServerList := make([]metadata.MySQLServer, result.RowNumber())
	for i := range mysqlServerList {
		mysqlServerList[i] = NewEmptyMySQLServerInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(mysqlServerList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return mysqlServerList, nil
}

// GetAppsByDBID gets an apps that use this db
func (dr *DBRepo) GetAppsByDBID(dbID int) ([]metadata.App, error) {
	sql := `
		SELECT ai.id, ai.app_name, ai.level, ai.del_flag, ai.create_time, ai.last_update_time
		FROM t_meta_app_info ai
			INNER JOIN t_meta_app_db_map adm ON ai.id = adm.app_id
			INNER JOIN t_meta_db_info di ON adm.db_id = di.id
		WHERE ai.del_flag = 0
		  AND adm.del_flag = 0
		  AND di.del_flag = 0
		  AND di.id = ? ;
	`
	log.Debugf("metadata DBRepo.GetAppsByDBID() sql: \n%s\nplaceholders: %d", sql, dbID)

	result, err := dr.Execute(sql, dbID)
	if err != nil {
		return nil, err
	}

	resultNum := result.RowNumber()
	appList := make([]metadata.App, resultNum)

	for row := constant.ZeroInt; row < resultNum; row++ {
		appList[row] = NewEmptyAppInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(appList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return appList, nil
}

// GetAppUsersByDBID gets the application users of the given id from the middleware
func (dr *DBRepo) GetAppUsersByDBID(id int) ([]metadata.User, error) {
	sql := `
		SELECT DISTINCT ui.id,
					    ui.user_name,
					    ui.department_name,
					    ui.employee_id,
					    ui.account_name,
					    ui.email,
					    ui.telephone,
					    ui.mobile,
					    ui.role,
					    ui.del_flag,
					    ui.create_time,
					    ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_app_user_map aum ON ui.id = aum.user_id
		    INNER JOIN t_meta_app_info ai ON aum.app_id = ai.id
			INNER JOIN t_meta_app_db_map adm ON ai.id = adm.app_id
			INNER JOIN t_meta_db_info di ON adm.db_id = di.id
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id AND di.cluster_type = 1
		WHERE ui.del_flag = 0
		  AND aum.del_flag = 0
		  AND ai.del_flag = 0
		  AND adm.del_flag = 0
		  AND di.del_flag = 0
		  AND mci.del_flag = 0
		  AND di.id = ? ;
	`
	log.Debugf("metadata DBRepo.GetAppUsersByDBID() sql: \n%s\nplaceholders: %d, %d", sql, id)

	result, err := dr.Execute(sql, id)
	if err != nil {
		return nil, err
	}

	resultNum := result.RowNumber()
	userList := make([]metadata.User, resultNum)

	for row := 0; row < resultNum; row++ {
		userList[row] = NewEmptyUserInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(userList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return userList, nil
}

// GetUsersByDBID gets the db users of the given id from the middleware
func (dr *DBRepo) GetUsersByDBID(id int) ([]metadata.User, error) {
	sql := `
		SELECT DISTINCT ui.id,
						ui.user_name,
						ui.department_name,
						ui.employee_id,
						ui.account_name,
						ui.email,
						ui.telephone,
						ui.mobile,
						ui.role,
						ui.del_flag,
						ui.create_time,
						ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_db_user_map dum ON ui.id = dum.user_id
			INNER JOIN t_meta_db_info di ON dum.db_id = di.id
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id AND di.cluster_type = 1
		WHERE ui.del_flag = 0
		  AND dum.del_flag = 0
		  AND di.del_flag = 0
		  AND mci.del_flag = 0
		  AND dum.db_id = ? ;
	`
	log.Debugf("metadata DBRepo.GetUsersByDBID() sql: \n%s\nplaceholders: %d, %d", sql, id)

	result, err := dr.Execute(sql, id)
	if err != nil {
		return nil, err
	}

	resultNum := result.RowNumber()
	userList := make([]metadata.User, resultNum)

	for row := 0; row < resultNum; row++ {
		userList[row] = NewEmptyUserInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(userList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return userList, nil
}

// GetAllUsersByDBID gets both application and db users of the given id from the middleware
func (dr *DBRepo) GetAllUsersByDBID(id int) ([]metadata.User, error) {
	sql := `
		SELECT DISTINCT ui.id,
			   			ui.user_name,
			   			ui.department_name,
			   			ui.employee_id,
			   			ui.account_name,
			   			ui.email,
			   			ui.telephone,
			   			ui.mobile,
			   			ui.role,
			   			ui.del_flag,
			   			ui.create_time,
			   			ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_app_user_map aum ON ui.id = aum.user_id
		    INNER JOIN t_meta_app_info ai ON aum.app_id = ai.id
			INNER JOIN t_meta_app_db_map adm ON aum.app_id = adm.app_id
			INNER JOIN t_meta_db_info di ON di.id = adm.db_id
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id AND di.cluster_type = 1
		WHERE ui.del_flag = 0
		  AND aum.del_flag = 0
		  AND ai.del_flag = 0
		  AND adm.del_flag = 0
		  AND di.del_flag = 0
		  AND mci.del_flag = 0
		  AND di.cluster_id = ?
		UNION
		SELECT DISTINCT ui.id,
					    ui.user_name,
					    ui.department_name,
					    ui.employee_id,
					    ui.account_name,
					    ui.email,
					    ui.telephone,
					    ui.mobile,
					    ui.role,
					    ui.del_flag,
					    ui.create_time,
					    ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_db_user_map dum ON ui.id = dum.user_id
			INNER JOIN t_meta_db_info di ON dum.db_id = di.id
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id AND di.cluster_type = 1
		WHERE ui.del_flag = 0
		  AND dum.del_flag = 0
		  AND di.del_flag = 0
		  AND mci.del_flag = 0
		  AND di.cluster_id = ?
		UNION
		SELECT DISTINCT ui.id,
						ui.user_name,
						ui.department_name,
						ui.employee_id,
						ui.account_name,
						ui.email,
						ui.telephone,
						ui.mobile,
						ui.role,
						ui.del_flag,
						ui.create_time,
						ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_mysql_cluster_user_map mcum ON ui.id = mcum.user_id
			INNER JOIN t_meta_mysql_cluster_info mci ON mci.id = mcum.mysql_cluster_id
			INNER JOIN t_meta_db_info di ON mci.id = di.cluster_id AND di.cluster_type = 1
		WHERE ui.del_flag = 0
		  AND mcum.del_flag = 0
		  AND mci.del_flag = 0
		  AND di.del_flag = 0
		  AND di.cluster_id = ?
		UNION
		SELECT DISTINCT ui.id,
			   			ui.user_name,
			   			ui.department_name,
			   			ui.employee_id,
			   			ui.account_name,
			   			ui.email,
			   			ui.telephone,
			   			ui.mobile,
			   			ui.role,
			   			ui.del_flag,
			   			ui.create_time,
			   			ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_resource_role_user_map rrum ON ui.id = rrum.user_id
			INNER JOIN t_meta_resource_role_info rri ON rrum.resource_role_id = rri.id
			INNER JOIN t_meta_resource_group_resource_role_map rgrrm ON rri.id = rgrrm.resource_role_id
			INNER JOIN t_meta_resource_group_info rgi ON rgrrm.resource_group_id = rgi.id
			INNER JOIN t_meta_mysql_cluster_resource_group_map mcrgm ON rgi.id = mcrgm.mysql_cluster_id
			INNER JOIN t_meta_mysql_cluster_info mci ON mcrgm.mysql_cluster_id = mci.id
			INNER JOIN t_meta_db_info di ON mci.id = di.cluster_id AND di.cluster_type = 1
		WHERE ui.del_flag = 0
		  AND rrum.del_flag = 0
		  AND rri.del_flag = 0
		  AND rgrrm.del_flag = 0
		  AND rgi.del_flag = 0
		  AND mcrgm.del_flag = 0
		  AND mci.del_flag = 0
		  AND di.del_flag = 0
		  AND di.cluster_id = ? ;
	`
	log.Debugf("metadata DBRepo.GetAllUsersByDBID() sql: \n%s\nplaceholders: %d, %d, %d, %d", sql, id, id, id, id)

	result, err := dr.Execute(sql, id, id, id, id)
	if err != nil {
		return nil, err
	}

	resultNum := result.RowNumber()
	userList := make([]metadata.User, resultNum)

	for row := 0; row < resultNum; row++ {
		userList[row] = NewEmptyUserInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(userList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return userList, nil
}

// Create creates a database in the middleware
func (dr *DBRepo) Create(db metadata.DB) (metadata.DB, error) {
	sql := `INSERT INTO t_meta_db_info(db_name, cluster_id, cluster_type, env_id) VALUES(?, ?, ?, ?);`
	log.Debugf("metadata DBRepo.Create() insert sql: %s", sql)

	// execute
	_, err := dr.Execute(sql, db.GetDBName(), db.GetClusterID(), db.GetClusterType(), db.GetEnvID())
	if err != nil {
		return nil, err
	}
	// get id
	id, err := dr.GetID(db.GetDBName(), db.GetClusterID(), db.GetClusterType())
	if err != nil {
		return nil, err
	}
	// get entity
	return dr.GetByID(id)
}

// Update updates the database in the middleware
func (dr *DBRepo) Update(db metadata.DB) error {
	sql := `UPDATE t_meta_db_info SET db_name = ?, cluster_id = ?, cluster_type = ?, env_id = ?, del_flag = ? WHERE id = ?;`
	log.Debugf("metadata DBRepo.Update() update sql: %s", sql)
	_, err := dr.Execute(sql, db.GetDBName(), db.GetClusterID(), db.GetClusterType(), db.GetEnvID(), db.GetDelFlag(), db.Identity())

	return err
}

// Delete deletes the database in the middleware
func (dr *DBRepo) Delete(id int) error {
	tx, err := dr.Transaction()
	if err != nil {
		return err
	}
	defer func() {
		err = tx.Close()
		if err != nil {
			log.Errorf("metadata DBRepo.Delete(): close database connection failed. err:\n%+v", err)
		}
	}()

	err = tx.Begin()
	if err != nil {
		return err
	}
	sql := `DELETE FROM t_meta_db_info WHERE id = ?;`
	log.Debugf("metadata DBRepo.Delete() delete sql(t_meta_db_info): %s", sql)
	_, err = dr.Execute(sql, id)
	if err != nil {
		return err
	}
	sql = `DELETE FROM t_meta_app_db_map WHERE db_id = ?;`
	log.Debugf("metadata DBRepo.Delete() delete sql(t_meta_app_db_map): %s", sql)
	_, err = dr.Execute(sql, id)
	if err != nil {
		return err
	}

	return tx.Commit()
}

// AddApp adds a new map of the app and database in the middleware
func (dr *DBRepo) AddApp(dbID, appID int) error {
	appRepo := NewAppRepoWithGlobal()
	_, err := appRepo.GetByID(appID)
	if err != nil {
		return err
	}
	_, err = dr.GetByID(dbID)
	if err != nil {
		return err
	}
	sql := `INSERT INTO t_meta_app_db_map(app_id, db_id) VALUES(?, ?);`
	log.Debugf("metadata DBRepo.AddApp() insert sql: %s", sql)
	_, err = dr.Execute(sql, appID, dbID)

	return err
}

// DeleteApp deletes a map of the app and database in the middleware
func (dr *DBRepo) DeleteApp(dbID, appID int) error {
	appRepo := NewAppRepoWithGlobal()
	_, err := appRepo.GetByID(appID)
	if err != nil {
		return err
	}
	_, err = dr.GetByID(dbID)
	if err != nil {
		return err
	}
	sql := `DELETE FROM t_meta_app_db_map WHERE app_id = ? AND db_id = ?;`
	log.Debugf("metadata DBRepo.DeleteApp() delete sql: %s", sql)
	_, err = dr.Execute(sql, appID, dbID)

	return err
}

// DBAddUser adds a new map of db and user in the middleware
func (dr *DBRepo) DBAddUser(dbID, userID int) error {
	userRepo := NewUserRepoWithGlobal()
	_, err := userRepo.GetByID(userID)
	if err != nil {
		return err
	}
	_, err = dr.GetByID(dbID)
	if err != nil {
		return err
	}
	sql := `INSERT INTO t_meta_db_user_map(db_id, user_id) VALUES(?, ?);`
	log.Debugf("metadata DBRepo.DBAddUser() insert sql: %s", sql)
	_, err = dr.Execute(sql, dbID, userID)

	return err
}

// DBDeleteUser delete the map of db and user in the middleware
func (dr *DBRepo) DBDeleteUser(dbID, userID int) error {
	userRepo := NewUserRepoWithGlobal()
	_, err := userRepo.GetByID(userID)
	if err != nil {
		return err
	}
	_, err = dr.GetByID(dbID)
	if err != nil {
		return err
	}
	sql := `DELETE FROM t_meta_db_user_map WHERE db_id = ? AND user_id = ?;`
	log.Debugf("metadata DBRepo.DBDeleteUser() delete sql: %s", sql)
	_, err = dr.Execute(sql, dbID, userID)

	return err
}
